package cn.iune.erp.inventory;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.NoResultException;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.justep.baas.Utils;
import com.justep.baas.data.DataUtils;
import com.justep.baas.data.Row;
import com.justep.baas.data.Table;
import com.justep.baas.data.Transform;

import cn.iune.common.base.BaseJPAImpl;




@Repository
public class FileItemDaoJPAImpl extends BaseJPAImpl<FileItem> implements IFileItemDao {

//	private final Logger log = LoggerFactory.getLogger(FileItemDaoJPAImpl.class);

	private String kSql;
	
//	@PersistenceContext 
//	private EntityManager em ;

	@Override
	public void insert(FileItem obj) {
		em.persist(obj);
	}

	@Override
	public void delete(String fID) {
		em.remove(em.getReference(FileItem.class, fID));
	}

	/*
	@Override
	public void delete(String fID, String userId) {
		FileItem obj = em.find(FileItem.class, fID);
		String itemNo = obj.getItemNo();
		String enterpriseNo = obj.getEnterpriseNo();
		
		int maxDel = this.getMaxDel(itemNo, enterpriseNo);
		System.out.println("maxDel-->"+maxDel);
		
		obj.setDel(maxDel+1);
		obj.setDelUserId(userId);
		obj.setDelDatetime(Utils.getCurrentDateTime());
		em.merge(obj);
	}
	*/
	
	@Override
	public FileItem update(FileItem obj) {
//		System.out.println("更新开始！");
		FileItem objNew = em.merge(obj);
//		System.out.println("更新完成！");
		return objNew;
	}

	@Override
	public FileItem getByID(String fID) {
		FileItem singleResult = em.find(FileItem.class, fID);
		return singleResult;
	}

	@Override
	public List<FileItem> getAll() {
		kSql="SELECT FileItem FROM FileItem FileItem ";
		Query query = em.createQuery(kSql);
		@SuppressWarnings("unchecked")
		List<FileItem> resultList = query.getResultList();
		return resultList;
	}

	
	@Override
	public FileItem getByNo(String itemNo, String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE ( FileItem.itemNo = :itemNo ) "
				+ " AND ( FileItem.fParent is null ) "
				+ " AND ( FileItem.enterpriseNo = :enterpriseNo ) "
//				+ " AND ( FileItem.del=0 ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("itemNo", itemNo);
		query.setParameter("enterpriseNo", enterpriseNo);
		FileItem singleResult = null;
		try{
			singleResult = (FileItem) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
			System.out.println(e.getMessage());
		}finally{
			
		}
		
		return singleResult;
	}
	
	@Override
	public FileItem getByName(String itemName, String itemProperty, String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE ( FileItem.itemName = :itemName ) "
				+ " AND ( FileItem.itemProperty = :itemProperty ) "
				+ " AND ( FileItem.fParent is null ) "
				+ " AND ( FileItem.enterpriseNo = :enterpriseNo ) "
//				+ " AND ( FileItem.del=0 ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("itemName", itemName);
		query.setParameter("itemProperty", itemProperty);
		query.setParameter("enterpriseNo", enterpriseNo);
		FileItem singleResult = null;
		try{
			singleResult = (FileItem) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
			System.out.println(e.getMessage());
		}finally{
			
		}
		
		return singleResult;
	}
	
	@Override
	public FileItem getByBarcode(String barcode, String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE ( FileItem.barcode = :barcode ) "
				+ " AND ( FileItem.enterpriseNo = :enterpriseNo ) "
//				+ " AND ( FileItem.del=0 ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("barcode", barcode);
		query.setParameter("enterpriseNo", enterpriseNo);
		FileItem singleResult = null;
		try{
			singleResult = (FileItem) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
			System.out.println(e.getMessage());
		}finally{
			
		}
		
		return singleResult;
	}
	
	@Override
	public FileItem getByProperty(String fParent, String itemProperty) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE ( FileItem.fParent = :fParent ) "
				+ " AND ( FileItem.itemProperty = :itemProperty ) "
//				+ " AND ( FileItem.del=0 ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("fParent", fParent);
		query.setParameter("itemProperty", itemProperty);
		FileItem singleResult = null;
		try{
			singleResult = (FileItem) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
			System.out.println(e.getMessage());
		}finally{
			
		}
		
		return singleResult;
	}
	
	//获取商品列表（不包含SKU级别，只包含顶级物料档案列表，用于网上商城或者是小程序商城使用）
	@Override
	public List<FileItem> getRootItemList(String enterpriseNo, Integer offset, Integer limit) {
		//1、定义sql语句
//		Query query = null;
//		List<Object> params = new ArrayList<Object>();
		Map<String, Object> params = new HashMap<String,Object>();
		
		if(enterpriseNo==null) {
			kSql="SELECT FileItem FROM FileItem FileItem "
					+ " WHERE ( FileItem.fParent is null ) "
//					+ " AND ( FileItem.del=0 ) "
					+ " ORDER BY FileItem.sortNo "
					;

//			query = em.createQuery(kSql);
		}else {
			kSql="SELECT FileItem FROM FileItem FileItem "
					+ " WHERE ( FileItem.fParent is null ) "
//					+ " AND ( FileItem.del=0 ) "
//					+ " AND ( FileItem.enterpriseNo = ? ) "
					+ " AND ( FileItem.enterpriseNo = :enterpriseNo ) "
					+ " ORDER BY FileItem.sortNo "
					;

//			query = em.createQuery(kSql);
//			query.setParameter("enterpriseNo", enterpriseNo);
			
//			params.add(enterpriseNo);
			params.put("enterpriseNo", enterpriseNo);
		}
		
		List<FileItem> resultList = super.query(kSql, params, offset, limit);
		
		return resultList;
	}
	
	//获取商品列表（无论是不是SKU级别，只包含叶子类型的物料档案列表，用于超市销售出库使用）
	@Override
	public List<FileItem> getLeafItemList(String enterpriseNo, Integer offset, Integer limit) {
		//1、定义sql语句
//		Query query = null;
//		List<Object> params = new ArrayList<Object>();
		Map<String, Object> params = new HashMap<String,Object>();
		
		if(enterpriseNo==null) {
			kSql="SELECT FileItem FROM FileItem FileItem "
					+ " WHERE ( FileItem.nodeKind = 'nkLeaf' ) "
//					+ " AND ( FileItem.del=0 ) "
					+ " ORDER BY FileItem.sortNo "
					;

//			query = em.createQuery(kSql);
		}else {
			kSql="SELECT FileItem FROM FileItem FileItem "
					+ " WHERE ( FileItem.nodeKind = 'nkLeaf' ) "
//					+ " AND ( FileItem.del=0 ) "
//					+ " AND ( FileItem.enterpriseNo = ? ) "
					+ " AND ( FileItem.enterpriseNo = :enterpriseNo ) "
					+ " ORDER BY FileItem.sortNo "
					;

//			query = em.createQuery(kSql);
//			query.setParameter("enterpriseNo", enterpriseNo);
			
//			params.add(enterpriseNo);
			params.put("enterpriseNo", enterpriseNo);
		}
		
		List<FileItem> resultList = super.query(kSql, params, offset, limit);
		
		return resultList;
	}
	
	@Override
	public List<FileItem> getSKUList(String fID) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE ( FileItem.fParent = :fID ) "
//				+ " AND ( FileItem.del=0 ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("fID", fID);
		
		@SuppressWarnings("unchecked")
		List<FileItem> resultList = query.getResultList();
		
		return resultList;
	}
	
	@Override
	public List<FileItem> getList(String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE FileItem.enterpriseNo = :enterpriseNo "
				+ " ORDER BY FileItem.levelCode DESC"
				;

		Query query = em.createQuery(kSql);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<FileItem> resultList = query.getResultList();
		
		return resultList;
	}
	
	//获取产品（成品、半成品、自制件、委外件、虚拟件）列表，不包含采购件，按低阶码倒排序
	@Override
	public List<FileItem> getProductList(String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE FileItem.enterpriseNo = :enterpriseNo "
				+ " AND  FileItem.process<>'P' "
				+ " ORDER BY FileItem.levelCode DESC"
				;

		Query query = em.createQuery(kSql);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<FileItem> resultList = query.getResultList();
		
		return resultList;
	}
	
	@Override
	public List<FileItem> getListByLevelCode(String levelCode, String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE ( FileItem.enterpriseNo = :enterpriseNo ) "
				+ " AND ( FileItem.levelCode= :levelCode ) "
//				+ " AND ( FileItem.del=0 ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("levelCode", levelCode);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<FileItem> resultList = query.getResultList();
		
		return resultList;
	}
	
	@Override
	public List<FileItem> filterByNo(String likeNo, String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE FileItem.itemNo LIKE CONCAT('%',:likeNo,'%') "
				+ " AND FileItem.enterpriseNo=:enterpriseNo "
				+ " ORDER BY FileItem.itemNo "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("likeNo", likeNo);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		query.setMaxResults(5);
		
		@SuppressWarnings("unchecked")
		List<FileItem> resultList = query.getResultList();
		
		return resultList;
	}

	@Override
	public void deleteSKU(String fParent) {
		//1、定义sql语句
		kSql="DELETE FROM FileItem FileItem "
				+ " WHERE ( FileItem.fParent = :fParent ) "
//				+ " AND ( FileItem.del=0 ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("fParent", fParent);
		
		query.executeUpdate();
	}
	

	/*
	@Override
	public FileItem getByName(String itemName, String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT FileItem FROM FileItem FileItem "
				+ " WHERE ( FileItem.itemName = :itemName ) "
				+ " AND ( FileItem.enterpriseNo = :enterpriseNo ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("itemName", itemName);
		query.setParameter("enterpriseNo", enterpriseNo);
		FileItem singleResult = null;
		try{
			singleResult = (FileItem) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
		}finally{
		}
		return singleResult;
	}
	*/


	/*
	public JSONObject query(JSONObject data) throws Exception {
		
		// 获取参数
		Object columns = data.getJSONObject("columns"); // 列定义
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		
		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (!CommonService.isEmptyString(search)) {
			// 增加过滤条件
			filters.add("unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (!CommonService.isEmptyString(filter)) {
			filters.add(filter);
		}

		
		// 按用户ID过滤，用于主从数据示例
		
//		String userID = data.getString("userID");
//		if (!CommonService.isEmptyString(userID)) {
//			filters.add("fUserID = ?");
//			sqlParams.add(userID);
//		}
		
		
		Table table = null;
		// 获取数据源连接
		Connection conn = (Connection) Util.getConnection(DATASOURCE_U2);
		try {
			// 执行单表数据查询，返回Table
			table = Util.queryData(conn, TABLE_U2_FILEUNIT, columns, filters, "cDatetime DESC", sqlParams, offset, limit);
		} finally {
			// 必须关闭数据源连接
			conn.close();
		}
		
		JSONObject json = Transform.tableToJson(table);

		return json;
	}


	public int save(JSONObject object) throws Exception {
		// TODO Auto-generated method stub

		log.info("@RequestParam------"+object.toJSONString());
		// JSON转换Table
		Table table = Transform.jsonToTable(object);

		// 获取数据源连接
		Connection conn = Util.getConnection(DATASOURCE_U2);
		try {
			// 开启事务
			conn.setAutoCommit(false);
			try {
				// 保存Table
				Util.saveData(conn, table, TABLE_U2_FILEUNIT);
				// 提交事务
				conn.commit();
			} catch (SQLException e) {
				// 如果发生异常，首先回滚事务，然后把异常继续抛出
				conn.rollback();
				throw e;
			}
		} finally {
			// 必须关闭数据源连接
			conn.close();
		}

		return 0;
	}
	*/
	
	
	@Override
	public JSONObject create(JSONObject data) {
		//缺省列
		String defColstr = Utils.getDefCols(FileItem.class);
		data.put("defColstr", defColstr);
		return Utils.newData(data);

	}
	
	/* (non-Javadoc)
	 * @see com.iune.erp.inventory.IFileItemService#queryJDBC(com.alibaba.fastjson.JSONObject)
	 */
	@Override
	public JSONObject query(JSONObject data) throws SQLException{
		
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fParent = data.getString("fParent");
//		log.info("limit-->"+limit);
//		log.info("offset-->"+offset);
//		log.info("filters-->"+filters);
//		log.info("sqlParams-->"+params);
		/*
		limit-->15
		offset-->0
		filters-->[unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?]
		sqlParams-->[%3%, %3%, %3%]
		*/		
		
		
		/*
		 * 4.1 截取第二个 '.' 之前的所有字符。
		
		mysql> select substring_index('www.example.com', '.', 2);
		+------------------------------------------------+
		| substring_index('www.example.com', '.', 2) |
		+------------------------------------------------+
		| www                                  |
		+------------------------------------------------+
		4.2 截取第二个 '.' （倒数）之后的所有字符。
		
		mysql> select substring_index('www.example.com', '.', -2);
		+-------------------------------------------------+
		| substring_index('www.example.com', '.', -2) |

		 */
		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
//		String select = "FileItem.*";
		String select = "FileItem.*"
				+ ",if(FileItem.itemProperty is null,FileItem.itemName,CONCAT(FileItem.itemName,'【',FileItem.itemProperty,'】')) as itemDescribe"
				+ ",IF(FileItem.itemQuantityL=0,0,CONVERT(FileItem.costingAmount/FileItem.itemQuantityL,decimal(38,6))) as costingAmountPer"
				+ ",(FileItem.materialStandardPer+FileItem.workingStandardPer+FileItem.manufactStandardPer+FileItem.processStandardPer) as costingStandardPer"
				+ ",(FileItem.materialStandardPerThis+FileItem.workingStandardPerThis+FileItem.manufactStandardPerThis+FileItem.processStandardPerThis) as costingStandardPerThis"
				
//				+ ",ItemClass.classLabel as classLabel"
//				+ ",ItemClass.classFullID as classFullID"
//				+ ",ItemClass.classFullLabel as classFullLabel"
//				+ ",ItemClass.rationSize as rationSize"
//				+ ",BomMain.fID as bomID"

//				+ ",FileStorage.storageType as storageType"
//				+ ",FileStorage.storageTypeLabel as storageTypeLabel"
//				+ ",substring_index(substring_index(FileStorage.storageFullNo,'/',2),'/',-1) as storageNo"
//				+ ",substring_index(substring_index(FileStorage.storageFullName,'/',2),'/',-1) as storageName"
//				+ ",FileStorage.storageFullName as floorNo"

//				+ ",FileProvider.storageTypeD as storageTypeD"
//				+ ",FileProvider.storageTypeDLabel as storageTypeDLabel"
//				+ ",FileProvider.providerNo as providerNo"
//				+ ",FileProvider.shortName as providerShortName"
//				+ ",FileProvider.providerName as providerName"

//				+ ",FileCustomer.storageTypeD as storageTypeC"
//				+ ",FileCustomer.storageTypeDLabel as storageTypeCLabel"
//				+ ",FileCustomer.customerNo as customerNo"
//				+ ",FileCustomer.shortName as customerShortName"
//				+ ",FileCustomer.customerName as customerName"

//				+ ",FileWorkcenter.workcenterNo as workcenterNo"
//				+ ",FileWorkcenter.workcenterName as workcenterName"
//				+ ",FileWorkcenter.workcenterLName as workcenterLName"
				
//				+ ",FileStorage.factoryID"
//				+ ",FileFactory.factoryNo"
//				+ ",FileFactory.factoryName"
				
				;
		
//		String from = "FileItem FileItem";
		String from = "FileItem FileItem "
//				+ " LEFT JOIN ItemClass ItemClass ON (FileItem.itemClassID = ItemClass.fID)"
//				+ " LEFT JOIN FileStorage FileStorage ON FileItem.storageID = FileStorage.fID"
//				+ " LEFT JOIN FileProvider FileProvider ON FileItem.providerID=FileProvider.fID "
//				+ " LEFT JOIN FileWorkcenter FileWorkcenter ON FileItem.workcenterID=FileWorkcenter.fID "
//				+ " LEFT JOIN FileFactory FileFactory ON FileStorage.factoryID = FileFactory.fID"
//				+ " LEFT JOIN FileCustomer FileCustomer ON FileItem.customerID=FileCustomer.fID "
				;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("FileItem.itemNo LIKE ? OR FileItem.itemName LIKE ? OR FileItem.comments LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("FileItem.fID='"+id+"'");
		}
		//处理fParent
		if (Utils.isNotEmptyString(fParent)) {
			filters.add("FileItem.fParent='"+fParent+"'");
		}
		
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters"); // 列定义
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
//				System.out.println("jsonFilter.getString(\"filter\")-->"+jsonFilter.getString("filter"));
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
		//不显示已经删除的数据
//		filters.add("FileItem.del=0");
		
		String where = "";
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		table.setOffset(offset);
		table.setTableName("FileItem");
		JSONObject json = Transform.tableToJson(table);
		
		return json;
	}
	
	/* (non-Javadoc)
	 * @see com.iune.erp.inventory.IFileItemService#queryJDBC(com.alibaba.fastjson.JSONObject)
	 */
	@Override
	public JSONObject querySimple(JSONObject data) throws SQLException{
		
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fParent = data.getString("fParent");
//		log.info("limit-->"+limit);
//		log.info("offset-->"+offset);
//		log.info("filters-->"+filters);
//		log.info("sqlParams-->"+params);
		/*
		limit-->15
		offset-->0
		filters-->[unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?]
		sqlParams-->[%3%, %3%, %3%]
		*/		
		
		
		/*
		 * 4.1 截取第二个 '.' 之前的所有字符。
		
		mysql> select substring_index('www.example.com', '.', 2);
		+------------------------------------------------+
		| substring_index('www.example.com', '.', 2) |
		+------------------------------------------------+
		| www                                  |
		+------------------------------------------------+
		4.2 截取第二个 '.' （倒数）之后的所有字符。
		
		mysql> select substring_index('www.example.com', '.', -2);
		+-------------------------------------------------+
		| substring_index('www.example.com', '.', -2) |

		 */
		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
//		String select = "FileItem.*";
		String select = "FileItem.*"
				+ ",if(FileItem.itemProperty is null,FileItem.itemName,CONCAT(FileItem.itemName,'【',FileItem.itemProperty,'】')) as itemDescribe"
				+ ",CONVERT(FileItem.costingAmount/FileItem.itemQuantityL,decimal(38,6)) as costingAmountPer"
				+ ",(FileItem.materialStandardPer+FileItem.workingStandardPer+FileItem.manufactStandardPer+FileItem.processStandardPer) as costingStandardPer"
				+ ",(FileItem.materialStandardPerThis+FileItem.workingStandardPerThis+FileItem.manufactStandardPerThis+FileItem.processStandardPerThis) as costingStandardPerThis"

//				+ ",ItemClass.classLabel as classLabel"
//				+ ",ItemClass.classFullID as classFullID"
//				+ ",ItemClass.classFullLabel as classFullLabel"
//				+ ",ItemClass.rationSize as rationSize"
//				+ ",BomMain.fID as bomID"

//				+ ",FileStorage.storageType as storageType"
//				+ ",FileStorage.storageTypeLabel as storageTypeLabel"
//				+ ",substring_index(substring_index(FileStorage.storageFullNo,'/',2),'/',-1) as storageNo"
//				+ ",substring_index(substring_index(FileStorage.storageFullName,'/',2),'/',-1) as storageName"
//				+ ",FileStorage.storageFullName as floorNo"

//				+ ",FileProvider.storageTypeD as storageTypeD"
//				+ ",FileProvider.storageTypeDLabel as storageTypeDLabel"
//				+ ",FileProvider.providerNo as providerNo"
//				+ ",FileProvider.shortName as providerShortName"
//				+ ",FileProvider.providerName as providerName"

//				+ ",FileCustomer.storageTypeD as storageTypeC"
//				+ ",FileCustomer.storageTypeDLabel as storageTypeCLabel"
//				+ ",FileCustomer.customerNo as customerNo"
//				+ ",FileCustomer.shortName as customerShortName"
//				+ ",FileCustomer.customerName as customerName"

//				+ ",FileWorkcenter.workcenterNo as workcenterNo"
//				+ ",FileWorkcenter.workcenterName as workcenterName"
//				+ ",FileWorkcenter.workcenterLName as workcenterLName"
				
//				+ ",FileStorage.factoryID"
//				+ ",FileFactory.factoryNo"
//				+ ",FileFactory.factoryName"
				
				;
		
//		String from = "FileItem FileItem";
		String from = "FileItem FileItem "
//				+ " LEFT JOIN ItemClass ItemClass ON (FileItem.itemClassID = ItemClass.fID)"
//				+ " LEFT JOIN FileStorage FileStorage ON FileItem.storageID = FileStorage.fID"
//				+ " LEFT JOIN FileProvider FileProvider ON FileItem.providerID=FileProvider.fID "
//				+ " LEFT JOIN FileWorkcenter FileWorkcenter ON FileItem.workcenterID=FileWorkcenter.fID "
//				+ " LEFT JOIN FileFactory FileFactory ON FileStorage.factoryID = FileFactory.fID"
//				+ " LEFT JOIN FileCustomer FileCustomer ON FileItem.customerID=FileCustomer.fID "
				;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("FileItem.itemNo LIKE ? OR FileItem.itemName LIKE ? OR FileItem.comments LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("FileItem.fID='"+id+"'");
		}
		//处理fParent
		if (Utils.isNotEmptyString(fParent)) {
			filters.add("FileItem.fParent='"+fParent+"'");
		}
		
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters"); // 列定义
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
//				System.out.println("jsonFilter.getString(\"filter\")-->"+jsonFilter.getString("filter"));
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
		//不显示已经删除的数据
//		filters.add("FileItem.del=0");
		
		//没有下面的三行代码，默认也是可以查询出同样的结果
//		if(columns==null){
//			columns = Utils.entityToDefColJSON(StorageItem.class);
//		}
		
		String where = "";
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		table.setOffset(offset);
		table.setTableName("FileItem");
		
		//JSONObject json = Transform.tableToJson(table);
		
		JSONArray arrayItem = new JSONArray();

		List<Row> list = table.getRows();
		for(Row row : list){
			JSONObject javaObject= Transform.rowToJavaObject(table, row);
			FileItem obj = JSON.toJavaObject(javaObject, FileItem.class);
			
			arrayItem.add(obj);
		}
		
		JSONObject json = new JSONObject();
		
		json.put("data", arrayItem);
		json.put("total", Integer.parseInt(total.toString()));
		json.put("code", 0);
		json.put("msg", "success");
		
		return json;
	}

	/*
	private int getMaxDel(String itemNo, String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT max(FileItem.del) FROM FileItem FileItem "
				+ " WHERE ( FileItem.itemNo = :itemNo ) "
				+ " AND ( FileItem.enterpriseNo = :enterpriseNo ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("itemNo", itemNo);
		query.setParameter("enterpriseNo", enterpriseNo);
		int singleResult = (int) query.getSingleResult();
		return singleResult;
	}
	*/

	@Override
	public String getMaxSortNoPlus(String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT max(FileItem.sortNo) FROM FileItem FileItem "
				+ " WHERE ( FileItem.enterpriseNo = :enterpriseNo ) "
				+ " AND FileItem.fParent is null "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("enterpriseNo", enterpriseNo);
		String singleResult = (String) query.getSingleResult();
		if(singleResult==null){
			singleResult = "0000000001";
		}else{
			int tmp = Integer.parseInt(singleResult);
			tmp++;
			singleResult = String.format("%010d", tmp);
		}
		return singleResult;
	}
	
}
